Stored Functions

Stored Functions#

A stored function is a kind of a stored program that can only return one value. It can be used in SQL statements in place of an expression. Common formulas or expressions that are used over and over again can be written in stored functions to make the code reusable. However, if a stored function that queries data from tables is used in a SQL statement, then it may slow down the speed of the query.

There are a number of differences when we compare stored functions to stored procedures. Stored procedures can call stored functions but the opposite is not possible. Stored functions can be used in SQL statements but stored procedures can only be called with the CALL keyword. That is why stored procedures are stored in compiled form where as stored functions are parsed and compiled at runtime. Return value is optional in stored procedures but a must in stored functions. Moreover, stored functions can only return one value but there is no such restriction on the number of return values in stored procedures. Stored functions only support IN parameter type while stored procedures can have IN, OUT and INOUT parameters. Error handling is not possible in stored functions.

The CREATE FUNCTION statement is used to create a stored function. The parameter list contains all the parameters of the function. Unlike stored procedures where the parameters could be IN, OUT or INOUT type, a stored function only takes IN parameters so there is no need to specify the type of parameters in the parameter list. Since the stored function can return only one value, the data type of the return value is specified after the RETURN keyword.

A stored function can be deterministic or non deterministic meaning that for the same input parameters, the result will either be the same or different. This can be specified by using the keywords DETERMINISTIC or NOT DETERMINISTIC. If this keyword is not specified, the type is set to NOT DETERMINISTIC by default. The function body must have at least one RETURN statement. When control reaches it, the stored function exits.

To view all functions in a database, SHOW FUNCTION STATUS statement is used. This results returned can be narrowed down based on the LIKE operator or any other condition specified in the optional WHERE clause. To delete a stored function, DROP FUNCTION keywords with the optional IF EXISTS clause is used.

Syntax#

DELIMITER **

CREATE FUNCTION function_name(parameter_list)

RETURNS datatype

[NOT] DETERMINISTIC

BEGIN

function body

END **

DELIMITER ;

SHOW FUNCTION STATUS [LIKE ‘pattern’ | WHERE condition];

DROP FUNCTION [IF EXISTS] function_name;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy-paste the command ./DataJek/Lessons/59lesson.sh and wait for the mysql prompt to start-up.

1. We will create a stored function to count the number of digital assets owned by an actor and return this number to the caller.

   DELIMITER **
   
   CREATE FUNCTION DigitalAssetCount(
    ID INT) 
   RETURNS VARCHAR(50)
   DETERMINISTIC
   BEGIN
    DECLARE ReturnMessage VARCHAR(50);
    DECLARE Number INT DEFAULT 0;
    SELECT COUNT(*) INTO Number FROM DigitalAssets WHERE ActorId = ID;
   
    IF Number = 0 THEN
        SET ReturnMessage = 'The Actor does not have any digital assets.';
    ELSE
        SET ReturnMessage = CONCAT('The Actor has ', Number, ' digital assets');
    END IF;
    
    -- return the customer level
    RETURN (ReturnMessage);
   END**
   DELIMITER ;
   

   To view the functions in a database, the SHOW FUNCTION STATUS statement is used.

   SHOW FUNCTION STATUS;
   

   Output of the above statement is captured in the widget below:

2. The above statement only shows those functions from the database currently used that the user has privilege to view. We can specify search condition in WHERE clause as follows:

   SHOW FUNCTION STATUS
   WHERE db = 'MovieIndustry';
   

   Similarly LIKE operator can also be used to narrow down the search results:

   SHOW FUNCTION STATUS 
   LIKE '%Count%';
   

   The output of the above statement is shown in the widget below:

3. The function DigitalAssetCount can be called in any SQL statement.

   SELECT Id, DigitalAssetCount(Id) AS Count
   FROM Actors;
   

4. The function can also be called from stored procedures. The code below creates a stored procedure GetDigitalAssetCount that calls the DigitalAssetCount function.

   DELIMITER **
   
   CREATE PROCEDURE GetDigitalAssetCount(
    IN  ActorNo INT,  
    OUT Message VARCHAR(50))
   BEGIN
    DECLARE Number INT DEFAULT 0;
    SET Number = ActorNo;    
    SET Message = DigitalAssetCount(Number);
   END**
   
   DELIMITER ;
   

   This is a contrived example where the stored procedure is called with an actor ID. This ID is then used inside the stored procedure to call the stored function.

   CALL GetDigitalAssetCount(10,@assetcount);
   SELECT @assetcount;
   

5. The DigitalAssetCount function is a deterministic function as it returns the same output when the function is called with the same input parameter. Nondeterministic functions are those that may return different outputs for the same input. This is because they use NOW(), RAND(), or any other similar function. As an example, we will create a function TimeSinceLastUpdate to find how much time has elapsed since an actor updated a particular digital asset. The function takes two input parameters, Actor ID and the Asset type and returns an INT value as the number of seconds that have passed.

   DELIMITER **
   
   CREATE FUNCTION TimeSinceLastUpdate(
                ID INT,
                Asset VARCHAR(15)) 
   RETURNS INT
   NOT DETERMINISTIC
   BEGIN
    DECLARE ElapsedTime INT;
   
    SELECT TIMESTAMPDIFF(SECOND, LastUpdatedOn, NOW())
    INTO ElapsedTime
    FROM DigitalAssets
    WHERE ActorID = ID AND AssetType = Asset;
   
    RETURN ElapsedTime;
   END**
   DELIMITER ;
   

   In the function body, we declare a variable for the elapsed time value and then use the TIMESTAMPDIFF function. We have chosen SECOND as the unit for TIMESTAMPDIFF as it clearly demonstrates the non deterministic nature of our function.

   SELECT TimeSinceLastUpdate(1,'Instagram');
   

   Calling this function again and again will result in a different output.

6. As mentioned in the lesson on creating and listing stored procedures, the ROUTINES tables in the information_schema database contains information about all functions in all the databases. The following query shows all the functions in the classicmodels database:

   SELECT routine_name
   FROM information_schema.routines
   WHERE routine_type = 'FUNCTION' AND routine_schema = 'MovieIndustry';
   

7. We will now see how to remove this stored function.

   DROP FUNCTION DigitalAssetCount;
   DROP FUNCTION IF EXISTS 
   DigitalAssetCount;
   

   When we try to drop a function that does not exist, a warning is issued. We can view the warning details using the SHOW WARNINGS statement:

   SHOW WARNINGS;